/*

Copyright 2010 Natan Vivo. All rights reserved.

Redistribution and use in source and binary forms, with or without modification, are
permitted provided that the following conditions are met:

   1. Redistributions of source code must retain the above copyright notice, this list of
      conditions and the following disclaimer.

   2. Redistributions in binary form must reproduce the above copyright notice, this list
      of conditions and the following disclaimer in the documentation and/or other materials
      provided with the distribution.

THIS SOFTWARE IS PROVIDED BY THE AUTHOR ``AS IS'' AND ANY EXPRESS OR IMPLIED
WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND
FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR OR
CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON
ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING
NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF
ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

*/

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;

namespace NVivo.Data
{
    public class DBHelper
    {
        #region Constructors

        public DBHelper(DbProviderFactory providerFactory, string connectionString)
        {
            if (providerFactory == null)
                throw new ArgumentNullException("providerFactory", "You must provide a DbProviderFactory instance.");

            if (String.IsNullOrEmpty(connectionString))
                throw new ArgumentException("The connection string cannot be empty.", "connectionString");
            
            _factory = providerFactory;
            _connectionString = connectionString;
        }

        public DBHelper(string connectionStringName)
        {
            ConnectionStringSettings css = ConfigurationManager.ConnectionStrings[connectionStringName];

            if (css == null)
                throw new ArgumentException("The connection string you specified does not exist in your configuration file.");

            _factory = DbProviderFactories.GetFactory(css.ProviderName);
            _connectionString = css.ConnectionString;
        }

        #endregion

        #region Public Fields and Methods

        private DbProviderFactory _factory;
        private string _connectionString;

        public DbProviderFactory Factory
        {
            get
            {
                return _factory;
            }
        }

        public string ConnectionString
        {
            get
            {
                return _connectionString;
            }
        }

        public DbConnection CreateConnection()
        {
            DbConnection connection = Factory.CreateConnection();
            connection.ConnectionString = ConnectionString;

            return connection;
        }

        public DbCommand CreateCommand(string commandText)
        {
            return CreateCommand(commandText, CommandType.Text);
        }

        public DbCommand CreateCommand(string commandText, CommandType commandType)
        {
            DbCommand command = Factory.CreateCommand();

            command.CommandText = commandText;
            command.CommandType = commandType;

            return command;
        }

        #endregion

        #region Private Helpers

        private static void SetCommandContext(DbCommand command, DbConnection connection, DbTransaction transaction)
        {
            if (transaction != null)
            {
                command.Connection = transaction.Connection;
                command.Transaction = transaction;
            }
            else
            {
                command.Connection = connection;
                command.Transaction = null;
            }
        }

        private static void FillFromReader(DbDataReader reader, int startRecord, int maxRecords, Action<DbDataReader> action)
        {
            if (startRecord > 0 || maxRecords > 0)
            {
                bool hasMoreRecords = true;

                while (startRecord > 0)
                {
                    if (!reader.Read())
                    {
                        hasMoreRecords = false;
                        break;
                    }

                    startRecord--;
                }

                if (hasMoreRecords)
                {
                    int i = 0;

                    while (i < maxRecords && reader.Read())
                    {
                        action(reader);
                        i++;
                    }
                }
            }
            else
            {
                while (reader.Read())
                    action(reader);
            }
        }

        #endregion

        #region ExecuteNonQuery

        private int ExecuteNonQueryInternal(DbCommand command, DbConnection connection, DbTransaction transaction)
        {
            SetCommandContext(command, connection, transaction);

            return command.ExecuteNonQuery();
        }

        public int ExecuteNonQuery(DbCommand command, DbConnection connection)
        {
            return ExecuteNonQueryInternal(command, connection, null);
        }

        public int ExecuteNonQuery(DbCommand command, DbTransaction transaction)
        {
            return ExecuteNonQueryInternal(command, null, transaction);
        }

        public int ExecuteNonQuery(DbCommand command)
        {
            int affectedRows;

            using (DbConnection connection = CreateConnection())
            {
                connection.Open();

                affectedRows = ExecuteNonQueryInternal(command, connection, null);

                connection.Close();
            }

            return affectedRows;
        }

        public int ExecuteNonQuery(string commandText, DbConnection connection)
        {
            return ExecuteNonQuery(CreateCommand(commandText, CommandType.Text), connection);
        }

        public int ExecuteNonQuery(string commandText, DbTransaction transaction)
        {
            return ExecuteNonQuery(CreateCommand(commandText, CommandType.Text), transaction);
        }

        public int ExecuteNonQuery(string commandText)
        {
            return ExecuteNonQuery(CreateCommand(commandText, CommandType.Text));
        }

        #endregion

        #region ExecuteScalar

        private object ExecuteScalarInternal(DbCommand command, DbConnection connection, DbTransaction transaction)
        {
            SetCommandContext(command, connection, transaction);

            return command.ExecuteScalar();
        }

        public object ExecuteScalar(DbCommand command, DbConnection connection)
        {
            return ExecuteScalarInternal(command, connection, null);
        }

        public object ExecuteScalar(DbCommand command, DbTransaction transaction)
        {
            return ExecuteScalarInternal(command, null, transaction);
        }

        public object ExecuteScalar(DbCommand command)
        {
            object o;

            using (DbConnection connection = CreateConnection())
            {
                connection.Open();

                o = ExecuteScalarInternal(command, connection, null);

                connection.Close();
            }

            return o;
        }

        public object ExecuteScalar(string commandText, DbConnection connection)
        {
            return ExecuteScalar(CreateCommand(commandText, CommandType.Text), connection);
        }

        public object ExecuteScalar(string commandText, DbTransaction transaction)
        {
            return ExecuteScalar(CreateCommand(commandText, CommandType.Text), transaction);
        }

        public object ExecuteScalar(string commandText)
        {
            return ExecuteScalar(CreateCommand(commandText, CommandType.Text));
        }

        #endregion

        #region ExecuteScalar<T> (with converters)

        private T ExecuteScalarInternal<T>(DbCommand command, Converter<object, T> converter, DbConnection connection, DbTransaction transaction)
        {
            object o = ExecuteScalarInternal(command, connection, transaction);
            return converter(o);
        }

        public T ExecuteScalar<T>(DbCommand command, Converter<object, T> converter, DbConnection connection)
        {
            return ExecuteScalarInternal<T>(command, converter, connection, null);
        }

        public T ExecuteScalar<T>(DbCommand command, Converter<object, T> converter, DbTransaction transaction)
        {
            return ExecuteScalarInternal<T>(command, converter, null, transaction);
        }

        public T ExecuteScalar<T>(DbCommand command, Converter<object, T> converter)
        {
            T o;

            using (DbConnection connection = CreateConnection())
            {
                connection.Open();

                o = ExecuteScalarInternal<T>(command, converter, connection, null);

                connection.Close();
            }

            return o;
        }

        public T ExecuteScalar<T>(string commandText, Converter<object, T> converter, DbConnection connection)
        {
            return ExecuteScalar<T>(CreateCommand(commandText, CommandType.Text), converter, connection);
        }

        public T ExecuteScalar<T>(string commandText, Converter<object, T> converter, DbTransaction transaction)
        {
            return ExecuteScalar<T>(CreateCommand(commandText, CommandType.Text), converter, transaction);
        }

        public T ExecuteScalar<T>(string commandText, Converter<object, T> converter)
        {
            return ExecuteScalar<T>(CreateCommand(commandText, CommandType.Text), converter);
        }

        #endregion

        #region ExecuteScalar<T>

        private T ExecuteScalarInternal<T>(DbCommand command, DbConnection connection, DbTransaction transaction)
        {
            object o = ExecuteScalarInternal(command, connection, transaction);
            return (T) o;
        }

        public T ExecuteScalar<T>(DbCommand command, DbConnection connection)
        {
            return ExecuteScalarInternal<T>(command, connection, null);
        }

        public T ExecuteScalar<T>(DbCommand command, DbTransaction transaction)
        {
            return ExecuteScalarInternal<T>(command, null, transaction);
        }

        public T ExecuteScalar<T>(DbCommand command)
        {
            T o;

            using (DbConnection connection = CreateConnection())
            {
                connection.Open();

                o = ExecuteScalarInternal<T>(command, connection, null);

                connection.Close();
            }

            return o;
        }

        public T ExecuteScalar<T>(string commandText, DbConnection connection)
        {
            return ExecuteScalar<T>(CreateCommand(commandText, CommandType.Text), connection);
        }

        public T ExecuteScalar<T>(string commandText, DbTransaction transaction)
        {
            return ExecuteScalar<T>(CreateCommand(commandText, CommandType.Text), transaction);
        }

        public T ExecuteScalar<T>(string commandText)
        {
            return ExecuteScalar<T>(CreateCommand(commandText, CommandType.Text));
        }

        #endregion

        #region ExecuteReader

        private DbDataReader ExecuteReaderInternal(DbCommand command, DbConnection connection, DbTransaction transaction)
        {
            SetCommandContext(command, connection, transaction);

            return command.ExecuteReader();
        }

        private DbDataReader ExecuteReaderInternal(DbCommand command)
        {
            DbConnection connection = CreateConnection();
            connection.Open();

            SetCommandContext(command, connection, null);

            return command.ExecuteReader(CommandBehavior.CloseConnection);
        }

        public DbDataReader ExecuteReader(DbCommand command, DbConnection connection)
        {
            return ExecuteReaderInternal(command, connection, null);
        }

        public DbDataReader ExecuteReader(DbCommand command, DbTransaction transaction)
        {
            return ExecuteReaderInternal(command, null, transaction);
        }

        public DbDataReader ExecuteReader(DbCommand command)
        {
            return ExecuteReaderInternal(command);
        }

        public DbDataReader ExecuteReader(string commandText, DbConnection connection)
        {
            return ExecuteReader(CreateCommand(commandText, CommandType.Text), connection);
        }

        public DbDataReader ExecuteReader(string commandText, DbTransaction transaction)
        {
            return ExecuteReader(CreateCommand(commandText, CommandType.Text), transaction);
        }

        public DbDataReader ExecuteReader(string commandText)
        {
            return ExecuteReader(CreateCommand(commandText, CommandType.Text));
        }

        #endregion

        #region ExecuteDataTable

        private DataTable ExecuteDataTableInternal(DbCommand command, int startRecord, int maxRecords, DbConnection connection, DbTransaction transaction)
        {
            SetCommandContext(command, connection, transaction);

            DbDataAdapter adapter = Factory.CreateDataAdapter();
            adapter.SelectCommand = command;

            DataTable dt = new DataTable();

            if (startRecord > 0 || maxRecords > 0)
                adapter.Fill(startRecord, maxRecords, dt);
            else
                adapter.Fill(dt);

            return dt;
        }

        private DataTable ExecuteDataTableInternal(DbCommand command, DbConnection connection, DbTransaction transaction)
        {
            return ExecuteDataTableInternal(command, 0, 0, connection, transaction);
        }

        public DataTable ExecuteDataTable(DbCommand command, int startRecord, int maxRecords, DbConnection connection)
        {
            return ExecuteDataTableInternal(command, startRecord, maxRecords, connection, null);
        }

        public DataTable ExecuteDataTable(DbCommand command, int startRecord, int maxRecords, DbTransaction transaction)
        {
            return ExecuteDataTableInternal(command, startRecord, maxRecords, null, transaction);
        }

        public DataTable ExecuteDataTable(DbCommand command, int startRecord, int maxRecords)
        {
            DataTable dt;

            using (DbConnection connection = CreateConnection())
            {
                connection.Open();

                dt = ExecuteDataTableInternal(command, startRecord, maxRecords, connection, null);
                
                connection.Close();
            }

            return dt;
        }

        public DataTable ExecuteDataTable(string commandText, int startRecord, int maxRecords, DbConnection connection)
        {
            return ExecuteDataTable(CreateCommand(commandText, CommandType.Text), startRecord, maxRecords, connection);
        }

        public DataTable ExecuteDataTable(string commandText, int startRecord, int maxRecords, DbTransaction transaction)
        {
            return ExecuteDataTable(CreateCommand(commandText, CommandType.Text), startRecord, maxRecords, transaction);
        }

        public DataTable ExecuteDataTable(string commandText, int startRecord, int maxRecords)
        {
            return ExecuteDataTable(CreateCommand(commandText, CommandType.Text), startRecord, maxRecords);
        }

        public DataTable ExecuteDataTable(DbCommand command, DbConnection connection)
        {
            return ExecuteDataTableInternal(command, connection, null);
        }

        public DataTable ExecuteDataTable(DbCommand command, DbTransaction transaction)
        {
            return ExecuteDataTableInternal(command, null, transaction);
        }

        public DataTable ExecuteDataTable(DbCommand command)
        {
            DataTable dt;

            using (DbConnection connection = CreateConnection())
            {
                connection.Open();

                dt = ExecuteDataTableInternal(command, connection, null);

                connection.Close();
            }

            return dt;
        }

        public DataTable ExecuteDataTable(string commandText, DbConnection connection)
        {
            return ExecuteDataTable(CreateCommand(commandText, CommandType.Text), connection);
        }

        public DataTable ExecuteDataTable(string commandText, DbTransaction transaction)
        {
            return ExecuteDataTable(CreateCommand(commandText, CommandType.Text), transaction);
        }

        public DataTable ExecuteDataTable(string commandText)
        {
            return ExecuteDataTable(CreateCommand(commandText, CommandType.Text));
        }

        #endregion

        #region ExecuteDataSet

        private DataSet ExecuteDataSetInternal(DbCommand command, DbConnection connection, DbTransaction transaction)
        {
            SetCommandContext(command, connection, transaction);

            DbDataAdapter adapter = Factory.CreateDataAdapter();
            adapter.SelectCommand = command;

            DataSet ds = new DataSet();
            adapter.Fill(ds);

            return ds;
        }

        public DataSet ExecuteDataSet(DbCommand command, DbConnection connection)
        {
            return ExecuteDataSetInternal(command, connection, null);
        }

        public DataSet ExecuteDataSet(DbCommand command, DbTransaction transaction)
        {
            return ExecuteDataSetInternal(command, null, transaction);
        }

        public DataSet ExecuteDataSet(DbCommand command)
        {
            DataSet ds;

            using (DbConnection connection = CreateConnection())
            {
                connection.Open();

                ds = ExecuteDataSetInternal(command, connection, null);

                connection.Close();
            }

            return ds;
        }

        public DataSet ExecuteDataSet(string commandText, DbConnection connection)
        {
            return ExecuteDataSet(CreateCommand(commandText, CommandType.Text), connection);
        }

        public DataSet ExecuteDataSet(string commandText, DbTransaction transaction)
        {
            return ExecuteDataSet(CreateCommand(commandText, CommandType.Text), transaction);
        }

        public DataSet ExecuteDataSet(string commandText)
        {
            return ExecuteDataSet(CreateCommand(commandText, CommandType.Text));
        }

        #endregion

        #region ExecuteArray<T> (with converters)

        private T[] ExecuteArrayInternal<T>(DbCommand command, Converter<object, T> converter, int startRecord, int maxRecords, DbConnection connection, DbTransaction transaction)
        {
            List<T> list = new List<T>();

            using (DbDataReader reader = ExecuteReaderInternal(command, connection, transaction))
            {
                FillFromReader(reader, startRecord, maxRecords, r => list.Add(converter(r.GetValue(0))));
                reader.Close();
            }

            return list.ToArray();
        }

        private T[] ExecuteArrayInternal<T>(DbCommand command, Converter<object, T> converter, DbConnection connection, DbTransaction transaction)
        {
            return ExecuteArrayInternal<T>(command, converter, 0, 0, connection, transaction);
        }

        public T[] ExecuteArray<T>(DbCommand command, Converter<object, T> converter, int startRecord, int maxRecords, DbConnection connection)
        {
            return ExecuteArrayInternal<T>(command, converter, startRecord, maxRecords, connection, null);
        }

        public T[] ExecuteArray<T>(DbCommand command, Converter<object, T> converter, int startRecord, int maxRecords, DbTransaction transaction)
        {
            return ExecuteArrayInternal<T>(command, converter, startRecord, maxRecords, null, transaction);
        }

        public T[] ExecuteArray<T>(DbCommand command, Converter<object, T> converter, int startRecord, int maxRecords)
        {
            T[] arr;

            using (DbConnection connection = CreateConnection())
            {
                connection.Open();

                arr = ExecuteArrayInternal<T>(command, converter, startRecord, maxRecords, connection, null);

                connection.Close();
            }

            return arr;
        }

        public T[] ExecuteArray<T>(string commandText, Converter<object, T> converter, int startRecord, int maxRecords, DbConnection connection)
        {
            return ExecuteArray<T>(CreateCommand(commandText, CommandType.Text), converter, startRecord, maxRecords, connection);
        }

        public T[] ExecuteArray<T>(string commandText, Converter<object, T> converter, int startRecord, int maxRecords, DbTransaction transaction)
        {
            return ExecuteArray<T>(CreateCommand(commandText, CommandType.Text), converter, startRecord, maxRecords, transaction);
        }

        public T[] ExecuteArray<T>(string commandText, Converter<object, T> converter, int startRecord, int maxRecords)
        {
            return ExecuteArray<T>(CreateCommand(commandText, CommandType.Text), converter, startRecord, maxRecords);
        }

        public T[] ExecuteArray<T>(DbCommand command, Converter<object, T> converter, DbConnection connection)
        {
            return ExecuteArrayInternal<T>(command, converter, connection, null);
        }

        public T[] ExecuteArray<T>(DbCommand command, Converter<object, T> converter, DbTransaction transaction)
        {
            return ExecuteArrayInternal<T>(command, converter, null, transaction);
        }

        public T[] ExecuteArray<T>(DbCommand command, Converter<object, T> converter)
        {
            T[] arr;

            using (DbConnection connection = CreateConnection())
            {
                connection.Open();

                arr = ExecuteArrayInternal<T>(command, converter, connection, null);

                connection.Close();
            }

            return arr;
        }

        public T[] ExecuteArray<T>(string commandText, Converter<object, T> converter, DbConnection connection)
        {
            return ExecuteArray<T>(CreateCommand(commandText, CommandType.Text), converter, connection);
        }

        public T[] ExecuteArray<T>(string commandText, Converter<object, T> converter, DbTransaction transaction)
        {
            return ExecuteArray<T>(CreateCommand(commandText, CommandType.Text), converter, transaction);
        }

        public T[] ExecuteArray<T>(string commandText, Converter<object, T> converter)
        {
            return ExecuteArray<T>(CreateCommand(commandText, CommandType.Text), converter);
        }

        #endregion

        #region ExecuteArray<T>

        private T[] ExecuteArrayInternal<T>(DbCommand command, int startRecord, int maxRecords, DbConnection connection, DbTransaction transaction)
        {
            List<T> list = new List<T>();

            using (DbDataReader reader = ExecuteReaderInternal(command, connection, transaction))
            {
                FillFromReader(reader, startRecord, maxRecords, r => list.Add((T) r.GetValue(0)));
                reader.Close();
            }

            return list.ToArray();
        }

        private T[] ExecuteArrayInternal<T>(DbCommand command, DbConnection connection, DbTransaction transaction)
        {
            return ExecuteArrayInternal<T>(command, 0, 0, connection, transaction);
        }

        public T[] ExecuteArray<T>(DbCommand command, int startRecord, int maxRecords, DbConnection connection)
        {
            return ExecuteArrayInternal<T>(command, startRecord, maxRecords, connection, null);
        }

        public T[] ExecuteArray<T>(DbCommand command, int startRecord, int maxRecords, DbTransaction transaction)
        {
            return ExecuteArrayInternal<T>(command, startRecord, maxRecords, null, transaction);
        }

        public T[] ExecuteArray<T>(DbCommand command, int startRecord, int maxRecords)
        {
            T[] arr;

            using (DbConnection connection = CreateConnection())
            {
                connection.Open();

                arr = ExecuteArrayInternal<T>(command, startRecord, maxRecords, connection, null);

                connection.Close();
            }

            return arr;
        }

        public T[] ExecuteArray<T>(string commandText, int startRecord, int maxRecords, DbConnection connection)
        {
            return ExecuteArray<T>(CreateCommand(commandText, CommandType.Text), startRecord, maxRecords, connection);
        }

        public T[] ExecuteArray<T>(string commandText, int startRecord, int maxRecords, DbTransaction transaction)
        {
            return ExecuteArray<T>(CreateCommand(commandText, CommandType.Text), startRecord, maxRecords, transaction);
        }

        public T[] ExecuteArray<T>(string commandText, int startRecord, int maxRecords)
        {
            return ExecuteArray<T>(CreateCommand(commandText, CommandType.Text), startRecord, maxRecords);
        }

        public T[] ExecuteArray<T>(DbCommand command, DbConnection connection)
        {
            return ExecuteArrayInternal<T>(command, connection, null);
        }

        public T[] ExecuteArray<T>(DbCommand command, DbTransaction transaction)
        {
            return ExecuteArrayInternal<T>(command, null, transaction);
        }

        public T[] ExecuteArray<T>(DbCommand command)
        {
            T[] arr;

            using (DbConnection connection = CreateConnection())
            {
                connection.Open();

                arr = ExecuteArrayInternal<T>(command, connection, null);

                connection.Close();
            }

            return arr;
        }

        public T[] ExecuteArray<T>(string commandText, DbConnection connection)
        {
            return ExecuteArray<T>(CreateCommand(commandText, CommandType.Text), connection);
        }

        public T[] ExecuteArray<T>(string commandText, DbTransaction transaction)
        {
            return ExecuteArray<T>(CreateCommand(commandText, CommandType.Text), transaction);
        }

        public T[] ExecuteArray<T>(string commandText)
        {
            return ExecuteArray<T>(CreateCommand(commandText, CommandType.Text));
        }

        #endregion

        #region ExecuteDictionary<TKey, TValue> (with converters)

        private IDictionary<TKey, TValue> ExecuteDictionaryInternal<TKey, TValue>(DbCommand command, Converter<object, TKey> keyConverter, Converter<object, TValue> valueConverter, int startRecord, int maxRecords, DbConnection connection, DbTransaction transaction)
        {
            IDictionary<TKey, TValue> dict = new Dictionary<TKey, TValue>();

            using (DbDataReader reader = ExecuteReaderInternal(command, connection, transaction))
            {
                FillFromReader(reader, startRecord, maxRecords, r => 
                    dict.Add(keyConverter(r.GetValue(0)), valueConverter(r.GetValue(1)))
                );

                reader.Close();
            }

            return dict;
        }

        private IDictionary<TKey, TValue> ExecuteDictionaryInternal<TKey, TValue>(DbCommand command, Converter<object, TKey> keyConverter, Converter<object, TValue> valueConverter, DbConnection connection, DbTransaction transaction)
        {
            return ExecuteDictionaryInternal<TKey, TValue>(command, keyConverter, valueConverter, 0, 0, connection, transaction);
        }

        public IDictionary<TKey, TValue> ExecuteDictionary<TKey, TValue>(DbCommand command, Converter<object, TKey> keyConverter, Converter<object, TValue> valueConverter, int startRecord, int maxRecords, DbConnection connection)
        {
            return ExecuteDictionaryInternal<TKey, TValue>(command, keyConverter, valueConverter, startRecord, maxRecords, connection, null);
        }

        public IDictionary<TKey, TValue> ExecuteDictionary<TKey, TValue>(DbCommand command, Converter<object, TKey> keyConverter, Converter<object, TValue> valueConverter, int startRecord, int maxRecords, DbTransaction transaction)
        {
            return ExecuteDictionaryInternal<TKey, TValue>(command, keyConverter, valueConverter, startRecord, maxRecords, null, transaction);
        }

        public IDictionary<TKey, TValue> ExecuteDictionary<TKey, TValue>(DbCommand command, Converter<object, TKey> keyConverter, Converter<object, TValue> valueConverter, int startRecord, int maxRecords)
        {
            IDictionary<TKey, TValue> dict;

            using (DbConnection connection = CreateConnection())
            {
                connection.Open();

                dict = ExecuteDictionaryInternal<TKey, TValue>(command, keyConverter, valueConverter, startRecord, maxRecords, connection, null);

                connection.Close();
            }

            return dict;
        }

        public IDictionary<TKey, TValue> ExecuteDictionary<TKey, TValue>(string commandText, Converter<object, TKey> keyConverter, Converter<object, TValue> valueConverter, int startRecord, int maxRecords, DbConnection connection)
        {
            return ExecuteDictionary<TKey, TValue>(CreateCommand(commandText, CommandType.Text), keyConverter, valueConverter, startRecord, maxRecords, connection);
        }

        public IDictionary<TKey, TValue> ExecuteDictionary<TKey, TValue>(string commandText, Converter<object, TKey> keyConverter, Converter<object, TValue> valueConverter, int startRecord, int maxRecords, DbTransaction transaction)
        {
            return ExecuteDictionary<TKey, TValue>(CreateCommand(commandText, CommandType.Text), keyConverter, valueConverter, startRecord, maxRecords, transaction);
        }

        public IDictionary<TKey, TValue> ExecuteDictionary<TKey, TValue>(string commandText, Converter<object, TKey> keyConverter, Converter<object, TValue> valueConverter, int startRecord, int maxRecords)
        {
            return ExecuteDictionary<TKey, TValue>(CreateCommand(commandText, CommandType.Text), keyConverter, valueConverter, startRecord, maxRecords);
        }

        public IDictionary<TKey, TValue> ExecuteDictionary<TKey, TValue>(DbCommand command, Converter<object, TKey> keyConverter, Converter<object, TValue> valueConverter, DbConnection connection)
        {
            return ExecuteDictionaryInternal<TKey, TValue>(command, keyConverter, valueConverter, connection, null);
        }

        public IDictionary<TKey, TValue> ExecuteDictionary<TKey, TValue>(DbCommand command, Converter<object, TKey> keyConverter, Converter<object, TValue> valueConverter, DbTransaction transaction)
        {
            return ExecuteDictionaryInternal<TKey, TValue>(command, keyConverter, valueConverter, null, transaction);
        }

        public IDictionary<TKey, TValue> ExecuteDictionary<TKey, TValue>(DbCommand command, Converter<object, TKey> keyConverter, Converter<object, TValue> valueConverter)
        {
            IDictionary<TKey, TValue> dict;

            using (DbConnection connection = CreateConnection())
            {
                connection.Open();

                dict = ExecuteDictionaryInternal<TKey, TValue>(command, keyConverter, valueConverter, connection, null);

                connection.Close();
            }

            return dict;
        }

        public IDictionary<TKey, TValue> ExecuteDictionary<TKey, TValue>(string commandText, Converter<object, TKey> keyConverter, Converter<object, TValue> valueConverter, DbConnection connection)
        {
            return ExecuteDictionary<TKey, TValue>(CreateCommand(commandText, CommandType.Text), keyConverter, valueConverter, connection);
        }

        public IDictionary<TKey, TValue> ExecuteDictionary<TKey, TValue>(string commandText, Converter<object, TKey> keyConverter, Converter<object, TValue> valueConverter, DbTransaction transaction)
        {
            return ExecuteDictionary<TKey, TValue>(CreateCommand(commandText, CommandType.Text), keyConverter, valueConverter, transaction);
        }

        public IDictionary<TKey, TValue> ExecuteDictionary<TKey, TValue>(string commandText, Converter<object, TKey> keyConverter, Converter<object, TValue> valueConverter)
        {
            return ExecuteDictionary<TKey, TValue>(CreateCommand(commandText, CommandType.Text), keyConverter, valueConverter);
        }

        #endregion

        #region ExecuteDictionary<TKey, TValue>

        private IDictionary<TKey, TValue> ExecuteDictionaryInternal<TKey, TValue>(DbCommand command, int startRecord, int maxRecords, DbConnection connection, DbTransaction transaction)
        {
            IDictionary<TKey, TValue> dict = new Dictionary<TKey, TValue>();

            using (DbDataReader reader = ExecuteReaderInternal(command, connection, transaction))
            {
                FillFromReader(reader, startRecord, maxRecords, r =>
                    dict.Add((TKey) r.GetValue(0), (TValue) r.GetValue(1))
                );

                reader.Close();
            }

            return dict;
        }

        private IDictionary<TKey, TValue> ExecuteDictionaryInternal<TKey, TValue>(DbCommand command, DbConnection connection, DbTransaction transaction)
        {
            return ExecuteDictionaryInternal<TKey, TValue>(command, 0, 0, connection, transaction);
        }

        public IDictionary<TKey, TValue> ExecuteDictionary<TKey, TValue>(DbCommand command, int startRecord, int maxRecords, DbConnection connection)
        {
            return ExecuteDictionaryInternal<TKey, TValue>(command, startRecord, maxRecords, connection, null);
        }

        public IDictionary<TKey, TValue> ExecuteDictionary<TKey, TValue>(DbCommand command, int startRecord, int maxRecords, DbTransaction transaction)
        {
            return ExecuteDictionaryInternal<TKey, TValue>(command, startRecord, maxRecords, null, transaction);
        }

        public IDictionary<TKey, TValue> ExecuteDictionary<TKey, TValue>(DbCommand command, int startRecord, int maxRecords)
        {
            IDictionary<TKey, TValue> dict;

            using (DbConnection connection = CreateConnection())
            {
                connection.Open();

                dict = ExecuteDictionaryInternal<TKey, TValue>(command, startRecord, maxRecords, connection, null);

                connection.Close();
            }

            return dict;
        }

        public IDictionary<TKey, TValue> ExecuteDictionary<TKey, TValue>(string commandText, int startRecord, int maxRecords, DbConnection connection)
        {
            return ExecuteDictionary<TKey, TValue>(CreateCommand(commandText, CommandType.Text), startRecord, maxRecords, connection);
        }

        public IDictionary<TKey, TValue> ExecuteDictionary<TKey, TValue>(string commandText, int startRecord, int maxRecords, DbTransaction transaction)
        {
            return ExecuteDictionary<TKey, TValue>(CreateCommand(commandText, CommandType.Text), startRecord, maxRecords, transaction);
        }

        public IDictionary<TKey, TValue> ExecuteDictionary<TKey, TValue>(string commandText, int startRecord, int maxRecords)
        {
            return ExecuteDictionary<TKey, TValue>(CreateCommand(commandText, CommandType.Text), startRecord, maxRecords);
        }

        public IDictionary<TKey, TValue> ExecuteDictionary<TKey, TValue>(DbCommand command, DbConnection connection)
        {
            return ExecuteDictionaryInternal<TKey, TValue>(command, connection, null);
        }

        public IDictionary<TKey, TValue> ExecuteDictionary<TKey, TValue>(DbCommand command, DbTransaction transaction)
        {
            return ExecuteDictionaryInternal<TKey, TValue>(command, null, transaction);
        }

        public IDictionary<TKey, TValue> ExecuteDictionary<TKey, TValue>(DbCommand command)
        {
            IDictionary<TKey, TValue> dict;

            using (DbConnection connection = CreateConnection())
            {
                connection.Open();

                dict = ExecuteDictionaryInternal<TKey, TValue>(command, connection, null);

                connection.Close();
            }

            return dict;
        }

        public IDictionary<TKey, TValue> ExecuteDictionary<TKey, TValue>(string commandText, DbConnection connection)
        {
            return ExecuteDictionary<TKey, TValue>(CreateCommand(commandText, CommandType.Text), connection);
        }

        public IDictionary<TKey, TValue> ExecuteDictionary<TKey, TValue>(string commandText, DbTransaction transaction)
        {
            return ExecuteDictionary<TKey, TValue>(CreateCommand(commandText, CommandType.Text), transaction);
        }

        public IDictionary<TKey, TValue> ExecuteDictionary<TKey, TValue>(string commandText)
        {
            return ExecuteDictionary<TKey, TValue>(CreateCommand(commandText, CommandType.Text));
        }

        #endregion

        #region ExecuteObject<T>

        private T ExecuteObjectInternal<T>(DbCommand command, Converter<DbDataReader, T> converter, DbConnection connection, DbTransaction transaction)
        {
            T o;

            using (DbDataReader reader = ExecuteReaderInternal(command, connection, transaction))
            {
                if (reader.Read())
                    o = converter(reader);
                else
                    o = default(T);

                reader.Close();
            }

            return o;
        }

        public T ExecuteObject<T>(DbCommand command, Converter<DbDataReader, T> converter, DbConnection connection)
        {
            return ExecuteObjectInternal<T>(command, converter, connection, null);
        }

        public T ExecuteObject<T>(DbCommand command, Converter<DbDataReader, T> converter, DbTransaction transaction)
        {
            return ExecuteObjectInternal<T>(command, converter, null, transaction);
        }

        public T ExecuteObject<T>(DbCommand command, Converter<DbDataReader, T> converter)
        {
            T o;

            using (DbConnection connection = CreateConnection())
            {
                connection.Open();

                o = ExecuteObjectInternal<T>(command, converter, connection, null);

                connection.Close();
            }

            return o;
        }

        public T ExecuteObject<T>(string commandText, Converter<DbDataReader, T> converter, DbConnection connection)
        {
            return ExecuteObject<T>(CreateCommand(commandText, CommandType.Text), converter, connection);
        }

        public T ExecuteObject<T>(string commandText, Converter<DbDataReader, T> converter, DbTransaction transaction)
        {
            return ExecuteObject<T>(CreateCommand(commandText, CommandType.Text), converter, transaction);
        }

        public T ExecuteObject<T>(string commandText, Converter<DbDataReader, T> converter)
        {
            return ExecuteObject<T>(CreateCommand(commandText, CommandType.Text), converter);
        }

        #endregion

        #region ExecuteCollection<T> (with converters)

        private ICollection<T> ExecuteCollectionInternal<T>(DbCommand command, Converter<DbDataReader, T> converter, ICollection<T> collection, int startRecord, int maxRecords, DbConnection connection, DbTransaction transaction)
        {
            using (DbDataReader reader = ExecuteReaderInternal(command, connection, transaction))
            {
                FillFromReader(reader, startRecord, maxRecords, r => collection.Add(converter(reader)));
                reader.Close();
            }

            return collection;
        }

        private ICollection<T> ExecuteCollectionInternal<T>(DbCommand command, Converter<DbDataReader, T> converter, ICollection<T> collection, DbConnection connection, DbTransaction transaction)
        {
            return ExecuteCollectionInternal<T>(command, converter, collection, 0, 0, connection, transaction);
        }

        public ICollection<T> ExecuteCollection<T>(DbCommand command, Converter<DbDataReader, T> converter, ICollection<T> collection, int startRecord, int maxRecords, DbConnection connection)
        {
            return ExecuteCollectionInternal<T>(command, converter, collection, startRecord, maxRecords, connection, null);
        }

        public ICollection<T> ExecuteCollection<T>(DbCommand command, Converter<DbDataReader, T> converter, ICollection<T> collection, int startRecord, int maxRecords, DbTransaction transaction)
        {
            return ExecuteCollectionInternal<T>(command, converter, collection, startRecord, maxRecords, null, transaction);
        }

        public ICollection<T> ExecuteCollection<T>(DbCommand command, Converter<DbDataReader, T> converter, ICollection<T> collection, int startRecord, int maxRecords)
        {
            using (DbConnection connection = CreateConnection())
            {
                connection.Open();

                ExecuteCollectionInternal<T>(command, converter, collection, startRecord, maxRecords, connection, null);

                connection.Close();
            }

            return collection;
        }

        public ICollection<T> ExecuteCollection<T>(string commandText, Converter<DbDataReader, T> converter, ICollection<T> collection, int startRecord, int maxRecords, DbConnection connection)
        {
            return ExecuteCollection<T>(CreateCommand(commandText, CommandType.Text), converter, collection, startRecord, maxRecords, connection);
        }

        public ICollection<T> ExecuteCollection<T>(string commandText, Converter<DbDataReader, T> converter, ICollection<T> collection, int startRecord, int maxRecords, DbTransaction transaction)
        {
            return ExecuteCollection<T>(CreateCommand(commandText, CommandType.Text), converter, collection, startRecord, maxRecords, transaction);
        }

        public ICollection<T> ExecuteCollection<T>(string commandText, Converter<DbDataReader, T> converter, ICollection<T> collection, int startRecord, int maxRecords)
        {
            return ExecuteCollection<T>(CreateCommand(commandText, CommandType.Text), converter, collection, startRecord, maxRecords);
        }

        public ICollection<T> ExecuteCollection<T>(DbCommand command, Converter<DbDataReader, T> converter, ICollection<T> collection, DbConnection connection)
        {
            return ExecuteCollectionInternal<T>(command, converter, collection, connection, null);
        }

        public ICollection<T> ExecuteCollection<T>(DbCommand command, Converter<DbDataReader, T> converter, ICollection<T> collection, DbTransaction transaction)
        {
            return ExecuteCollectionInternal<T>(command, converter, collection, null, transaction);
        }

        public ICollection<T> ExecuteCollection<T>(DbCommand command, Converter<DbDataReader, T> converter, ICollection<T> collection)
        {
            using (DbConnection connection = CreateConnection())
            {
                connection.Open();

                ExecuteCollectionInternal<T>(command, converter, collection, connection, null);

                connection.Close();
            }

            return collection;
        }

        public ICollection<T> ExecuteCollection<T>(string commandText, Converter<DbDataReader, T> converter, ICollection<T> collection, DbConnection connection)
        {
            return ExecuteCollection<T>(CreateCommand(commandText, CommandType.Text), converter, collection, connection);
        }

        public ICollection<T> ExecuteCollection<T>(string commandText, Converter<DbDataReader, T> converter, ICollection<T> collection, DbTransaction transaction)
        {
            return ExecuteCollection<T>(CreateCommand(commandText, CommandType.Text), converter, collection, transaction);
        }

        public ICollection<T> ExecuteCollection<T>(string commandText, Converter<DbDataReader, T> converter, ICollection<T> collection)
        {
            return ExecuteCollection<T>(CreateCommand(commandText, CommandType.Text), converter, collection);
        }

        #endregion

        #region AddParameter

        public DbParameter AddParameter(DbCommand command, string parameterName, object parameterValue)
        {
            DbParameter parameter = Factory.CreateParameter();

            parameter.ParameterName = parameterName;
            parameter.Value = parameterValue;

            command.Parameters.Add(parameter);

            return parameter;
        }

        public DbParameter AddParameter(DbCommand command, string parameterName)
        {
            return AddParameter(command, parameterName, null);
        }

        #endregion
   }
}
